package com.supermap.dataservice.user.dao;

import com.supermap.dataservice.util.Table;
import com.supermap.dubbo.api.common.entity.Area;
import com.supermap.dubbo.api.user.entity.*;
import org.apache.ibatis.annotations.*;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

/**
 * @Author:langhongshuai
 * @Description:用户数据访问层
 * @Date:18/1/3 上午10:42
 */
@Mapper
public interface UserDao {
    @Select("select count(id) from t_user where loginName=#{loginName}")
    Integer getUserLoginNameIsNotExist(@Param("loginName")String loginName);

    @Update("UPDATE t_user set longitude=#{longitude},latitude=#{latitude} where id = #{UserId}")
    int updateUserlatlongByUserId(@Param("UserId") String UserId, @Param("longitude") BigDecimal longitude, @Param("latitude") BigDecimal latitude);

    /**
     * 查看经纬度
     */
    @Select("select count(1) from t_area where areacode=#{areaCode} and latitude=#{latitude} and longitude=#{longitude}")
    Integer getAreaByList(@Param("areaCode") String areaCode, @Param("latitude") BigDecimal latitude, @Param("longitude") BigDecimal longitude);

    /**
     * 查询用户的经纬度
     */
    @Select("select areaCode,latitude,longitude from t_user where id=#{userId} ")
    Map<String, Object> getUserrlatlongByuserId(@Param("userId") String userId);

    /***
     * 根据id修改人员状态
     * ***/
    @Update("UPDATE t_user set state=#{state} where id = #{id}")
    int updateState(@Param("state") String state, @Param("id") int id);

    /***
     * 根据userId查询角色
     * ***/
    @Select("select b.name from t_user_role a join t_role b on a.roleid=b.id where a.userid=#{userId}")
    String queryRoleName(@Param("userId") int userId);

    /***
     * 插入关联id
     * **/
    @Insert("insert into vip_user_relation (vip_id,user_id) values(#{vipId},#{userId})")
    int insertVipUserRelation(@Param("vipId") int vipId, @Param("userId") int userId);

    /***
     * 查询用户id
     * **/
    @Select("select * from t_user where  del_flg=0 and mobile=#{mobile}")
    User queryVipUser(@Param("mobile") String mobile);

    //    查询VIP用户
    @Select("select * from t_vip where userId=#{userId}")
    public Vip getVip(@Param("userId") Integer userId);

    @SelectProvider(type = UserDao.VipUserEmeeySql.class, method = "getVipUserListSql")
    public List<User> getVipUserList(@Param("table") Table table, @Param("user") User user, @Param("vipId") int vipId);

    /**
     * 获取用户列表总数
     *
     * @param table
     * @return
     */
    @SelectProvider(type = UserDao.VipUserEmeeySql.class, method = "getUserListCountSql")
    public int getVipUserListCount(@Param("table") Table table, @Param("user") User user, @Param("vipId") int vipId);

    /**
     * 内部类 编写动态sql
     *
     * @author lxs
     */
    public class VipUserEmeeySql {

        //按条件分页查询用户列表
        public String getVipUserListSql(Map<String, Object> map) {

            Table table = (Table) map.get("table");
            //获取用户查询的条件
            User user = (User) map.get("user");
//            获取登陆用户id
            int vipId = (int) map.get("vipId");
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT t1.*,t4.name rolename,t5.areaname areaname FROM t_user t1\n" +
                    "LEFT JOIN t_user_role t2 on t1.id = t2.userid\n" +
                    "                    RIGHT  join vip_user_relation t3 on t1.id = t3.user_id  \n" +
                    "                     LEFT  JOIN t_role t4 ON t2.roleid = t4. CODE\n" +
                    "                    left join t_area t5 on t1.areaCode = t5.areacode\n" +
                    "where t1.id is not null ");
            sb.append(" and t1.del_flg=0\n");
            if (vipId > 0) {
                sb.append(" and t3.vip_id = " + vipId + "\n");
            }
            if (user.getUsername() != null) {
                sb.append(" and t1.username like '%" + user.getUsername() + "%'\n");
            }
            if (user.getMobile() != null) {
                sb.append("and t1.mobile like '%" + user.getMobile() + "%'\n");
            }
            if (user.getAreaCode() != null) {
                sb.append("and t1.areacode like '%" + user.getAreaCode() + "%'\n");
            }
            sb.append("group by id\n");
            sb.append("order by id asc limit\n" + table.getStartPoint() + "," + table.getLimit());
            sb.append(";");
            return sb.toString();
        }

        //按条件查询用户列表总数
        public String getUserListCountSql(Map<String, Object> map) {
            //获取分页数据
            Table table = (Table) map.get("table");
            //获取用户查询的条件
            User user = (User) map.get("user");
            //创建stringbuilder对象
            //            获取登陆用户id
            int vipId = (int) map.get("vipId");

            StringBuilder sql = new StringBuilder();
            sql.append("SELECT count(*) as count\n" +
                    " FROM t_user t1\n" +
                    " RIGHT  join vip_user_relation t4 on t1.id = t4.user_id " +
                    " LEFT JOIN t_role t2 ON t1.rolecode = t2. CODE\n" +
                    " left join t_area t3 on t1.areaCode = t3.areacode\n");
            sql.append(" where true\n");
            sql.append(" and t1.del_flg=0\n");
            if (vipId > 0) {
                sql.append(" and t4.vip_id = " + vipId + "\n");
            }
            if (user.getUsername() != null) {
                sql.append(" and t1.username like '%" + user.getUsername() + "%'\n");
            }
            if (user.getMobile() != null) {
                sql.append(" and t1.mobile like '%" + user.getMobile() + "%'\n");
            }
            if (user.getAreaCode() != null) {
                sql.append(" and t1.areacode like '%" + user.getAreaCode() + "%'\n");
            }
            if (user.getRolecode() != null) {
                sql.append(" and t2.code like '%" + user.getRolecode() + "%'\n");
            }
            sql.append(";");
            return sql.toString();
        }
    }

    /*****************************************************/

    /******
     * 查询用户名是否已绑定
     * ****/
    @Select("select * from bind_user where userId=#{userId}")
    BindUser queryBindUserName(@Param("userId") String userId);

    /***
     * 查询绑定用户名
     * ****/
    @Select("select count(1) from bind_user where loginName=#{loginName} and password=#{password}")
    public int queryBindUser(@Param("loginName") String loginName, @Param("password") String password);

    /****
     * 用户绑定
     * ***/
    @Insert("Insert into bind_user (userId,loginName,password) values(#{userId},#{loginName},#{password})")
    public void insertBindUser(@Param("userId") String userId, @Param("loginName") String loginName, @Param("password") String password);

    /**
     * 通过用户id逻辑删除用户
     *
     * @param userId
     */
    @Delete("update t_user set del_flg=1 where id=#{userId}")
    public void updateStatus(@Param("userId") int userId);

    /**
     * 通过用户名重置密码
     *
     * @param userId
     * @param newPassword
     */
    @Select("update t_user set password =#{newPassword} where id=#{userId}")
    public void resetPasswordCQ(@Param("userId") int userId, @Param("newPassword") String newPassword);

    /***
     * 根据userid查询用户
     * ****/
    @Select("select * from t_user where id = #{userId}")
    User queryUser(@Param("userId") int userId);

    /***
     * 获取角色信息
     * ***/
    @Select("SELECT * FROM `t_role`")
    List<Role> getRole();

    /***
     * 获取区域信息(使用范围：全国sms外部用户管理，重庆sms外部用户管理，精细化平台升级)
     * ***/
//    @Select("SELECT * FROM `t_area` where areacode like '50%'")
    @SelectProvider(type = UserSql.class, method = "getChinaArea")
    List<Area> getChinaArea(@Param("areaCode") String areaCode, @Param("type") String type);

    /**
     * 获取所有的省份
     **/
    @Select("select * from t_area where LENGTH(areacode)=2")
    List<Area> getPro();

    //重庆sms使用
    @Select("SELECT * FROM `t_area` where areacode like '50%'")
    List<Area> getAreaInfo();

    /**
     * 根据区域id获取区域信息
     **/
    @Select("SELECT * FROM `t_area` where areacode=#{areaCode}")
    Area getArea(@Param("areaCode") String areaCode);

    /***
     * 查询电话和登录名是否存在
     * ***/
    @Select("select count(*) from t_user where loginName = #{loginName} and del_flg=0")
    int queryLoginName(@Param("loginName") String loginName);

    /***
     * 查询电话号码是否存在
     * **/
    @Select("select count(*) from t_user where  del_flg=0 and mobile=#{mobile}")
    int queryMobile(@Param("mobile") String mobile);


    /**
     * 重庆增加用户
     *
     * @param user
     */
    @Insert("Insert into t_user(username,password,loginName,showName,address,email,mobile,Introduction,areaCode,latitude,longitude,del_flg,createtime) " +
            "values(#{user.username},#{user.password},#{user.loginName},#{user.showName},#{user.address},#{user.email},#{user.mobile},#{user.Introduction},#{user.areaCode},#{user.latitude},#{user.longitude},0,#{user.createtime})")
    public int insertCQ(@Param("user") InterNetInfo user);

    @Insert("Insert into t_user(username,password,loginName,showName,address,email,mobile,Introduction,areaCode,latitude,longitude,del_flg,createtime) " +
            "values(#{user.username},#{user.password},#{user.loginName},#{user.showName},#{user.address},#{user.Email},#{user.mobile},#{user.Introduction},#{user.areaCode},#{user.latitude},#{user.longitude},0,#{user.createtime})")
    public int insertAPPUser(@Param("us") User us);


    /**
     * 通过用户名和密码获取用户信息
     *
     * @param userName
     * @param password
     * @return
     */
    @Select("select * from t_user t1 where t1.loginName=#{userName} and t1.password=#{password}")
    public User checkUserByUP(@Param("userName") String userName, @Param("password") String password);

    /**
     * 通过用户名重置密码
     *
     * @param userName
     * @param newPassword
     */
    @Select("update t_user set password =#{newPassword} where username=#{userName}")
    public void resetPassword(@Param("userName") String userName, @Param("newPassword") String newPassword);

    /**
     * 通过（帐号或手机号）和密码登陆
     *
     * @param userName
     * @param password
     * @return
     */
    @Select("select t1.id,t1.username,t1.password,t1.loginName,t1.showName,t1.mobile,t1.address,(select areaname from t_area where areacode=t1.areaCode) as areaname,t1.Email,t1.HeadImage,t1.Introduction,t1.latitude,t1.longitude,t1.areaCode,t1.state,date_format(t1.createtime,'%Y-%m-%d %H:%i:%S') as createtime ,t3.name as rolename,t3.code as rolecode from t_user t1 \n" +
            "inner join t_user_role t2\n" +
            "on t1.id = t2.userid and t1.del_flg=0\n" +
            "inner join t_role t3\n" +
            "on t2.roleid =t3.id\n" +
            "where t1.loginName =#{userName} and t1.password=#{password}  ")
    public User Login(@Param("userName") String userName, @Param("password") String password);

    /**
     * 全国APP注册
     *
     * @param user
     */
    @InsertProvider(type = UserSql.class, method = "register")
    public void register(@Param("user") User user);

    /**
     * 通过账户名查找用户
     *
     * @param account
     * @return
     */
    @Select("Select * from t_user t1 where t1.loginName =#{account} or t1.mobile =#{account}")
    public List<User> checkMobileAndLoginName(@Param("account") String account);

    /**
     * 增加用户
     *
     * @param u
     */
    @Insert("Insert into t_user(username,password,loginName,mobile,areaCode,latitude,longitude) " +
            "values(#{username},#{password},#{loginName},#{mobile},#{areaCode},#{latitude},#{longitude})")
    public void insert(User u);

    /**
     * 通过登录名查找用户
     *
     * @param loginName
     * @return
     */
    @Select("select id,username from t_user where loginName=#{loginName} ")
    public User find(@Param("loginName") String loginName);

    /**
     * 通过登录名查找用户
     *
     * @param id
     * @return
     */
    @Select("select t1.* from t_user t1 where t1.id=#{id} ")
    public User findUserById(@Param("id") String id);

    /**
     * 通过区域code和角色获取用户信息
     *
     * @param areaCode
     * @param roleId
     * @return
     */
    @Select("select t1.id,t1.username,t1.address,t1.HeadImage,t1.Introduction from t_user t1 \n" +
            "INNER JOIN t_user_role t2\n" +
            "on t1.id=t2.userid\n" +
            "where \n" +
            "IF(ISNULL(#{areaCode}),true,t1.areaCode=#{areaCode} and t2.roleid=#{roleId});")
    public List<User> findUserByAreaRId(@Param("areaCode") String areaCode, @Param("roleId") int roleId);

    /**
     * 修改用户信息
     *
     * @param user
     * @return
     */
    @UpdateProvider(type = UserSql.class, method = "updateUserById_sql")
    int updateUserById(@Param("user") User user);

    /**
     * 获取增加用户后的id
     *
     * @return
     */
    @Select("Select max(id) as id from t_user")
    int selectId();

    /**
     * 分页显示用户列表
     *
     * @param table
     * @return
     */
    @SelectProvider(type = UserSql.class, method = "getUserListSql")
    public List<User> getUserList(@Param("table") Table table, @Param("user") User user);

    /***
     * App分页显示用户列表
     * **/
    @SelectProvider(type = UserSql.class, method = "getAppUserListSql")
    public List<User> getAppUserList(@Param("table") Table table, @Param("user") User user);

    /**
     * 获取用户列表总数AppApp
     *
     * @param table
     * @return
     */
    @SelectProvider(type = UserSql.class, method = "getUserListCountSql")
    public int getUserListCount(@Param("table") Table table, @Param("user") User user);

    /**
     * 模糊查询
     *
     * @param keyWord
     * @return
     */
    @SelectProvider(type = VipSql.class, method = "searchUserInfoByKeyWordSQL")
    List<User> searchUserInfoByKeyWord(@Param("keyWord") String keyWord, @Param("page") int page, @Param("count") int count, @Param("areaCode") String areaCode);

    /**
     * VIP用户搜索
     *
     * @param showName   用户名
     * @param type
     * @param areaCode
     * @param beginCount 起始搜索位置
     * @param count      搜索条数
     * @return
     */
    @Select("select v.*,v.Lat as latitude, v.Lon as longitude from t_vip v \n" +
            "JOIN t_user u on v.userId = u.id \n" +
            "JOIN t_user_role ur on ur.userid = u.id \n" +
            "JOIN t_role r on r.id = ur.roleid \n" +
            "where v.`name` like #{showName} \n" +
            "and if(isnull(#{areaCode}),true,v.areaCode like #{areaCode}) \n" +
            "and if(isnull(#{type}),true,r.code = #{type})\n" +
            "order by v.id \n" +
            "limit #{beginCount},#{count}")
    List<Vip> searchVip(String showName, String type, String areaCode, int beginCount, int count);

    /**
     * 修改用户信息
     *
     * @param user
     * @return
     */
    @UpdateProvider(type = UserSql.class,
            method = "updateUser")
    int updateUser(@Param("user") User user);

    /**
     * 通过用户id删除用户
     *
     * @param userId
     */
    @Delete("delete * from t_user where id=#{userId}")
    public void delUserById(@Param("userId") int userId);

    /**
     * 申请成为VIP
     *
     * @param vip
     * @return
     */
    @InsertProvider(type = VipSql.class,
            method = "applyForVIP")
    int applyForVIP(@Param("vip") Vip vip);

    @Select("select * from t_vip_apply where userid=#{id}")
    Vip findApplyVipById(@Param("id") String id);

    /**
     * 成为VIP
     *
     * @param vip
     * @return
     */
    @InsertProvider(type = VipSql.class,
            method = "affirmToVIP")
    int affirmToVIP(Vip vip);

    @Update("update t_vip_apply set del_flg=0 where userid=#{id}")
    int applyVIPFlag(@Param("id") String userId);

    @Select("select * from t_vip where userId=#{userId}")
    Vip findVipById(@Param("userId") String userId);

    class VipSql {
        public String searchUserInfoByKeyWordSQL(Map<String, Object> map) {
            String areaCode = (String) map.get("areaCode");
            String keyWord = (String) map.get("keyWord");
            int page = (int) map.get("page");
            int count = (int) map.get("count");
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.append("select t1.id,t1.showName,t1.username,t1.HeadImage,t1.address,t1.mobile,t1.longitude,t1.latitude,t3.code as roleCode   ,t3.name as roleName from t_user t1\n" +
                    "inner join t_user_role t2\n" +
                    "on t1.id=t2.userid\n" +
                    "inner join t_role t3\n" +
                    "on t2.roleid=t3.id  and t1.del_flg = 0 ");
            if (areaCode != null) {
                stringBuilder.append("  and t1.areaCode like '" + areaCode + "%'");
            }
            if (keyWord != null) {
                stringBuilder.append(" and (  t1.username like '%" + keyWord + "%' or t1.showName like '%" + keyWord + "%' or\n" +
                        " t1.address like '%" + keyWord + "%' or t1.mobile like  '%" + keyWord + "%' )");
            }
            stringBuilder.append(" limit " + page + "," + count + "");

            return stringBuilder.toString();
        }

        public String applyForVIP(Map<String, Object> map) {
            Vip vip = (Vip) map.get("vip");
            String table = "t_vip_apply";
            return getVipInsertSql(vip, table);
        }

        public String affirmToVIP(Map<String, Object> map) {
            Vip vip = (Vip) map.get("vip");
            String table = "t_vip";
            return getVipInsertSql(vip, table);
        }

        private String getVipInsertSql(Vip vip, String table) {
            StringBuilder sql = new StringBuilder();
            sql.append("insert into `").append(table).append("` ( `userId`, `name`, `type`, `CountyName`, `BelongsTownship`, `BelongsVillage`, `areaCode`, `Lon`, `Lat`, `height`, `BusinessScope`, `AreaScale`, `AnnualProductionValue`, `ProdOperaCount`, `TechCount`, `TechAverageAge`, `NetComputerAccess`, `useSmartPhones`, `SmartPhoneBrand`, `LeaderName`, `LeaderPhone`, `TechLeaderName`, `TechLeaderPhone`, `intro`, `epath`, `longitude`, `latitude`, `addr`, `del_flg`, `createtime`) values ( \n")
                    .append("'").append(vip.getUserId()).append("',")
                    .append("'").append(vip.getName()).append("',")
                    .append("'").append(vip.getType()).append("',")
                    .append("'").append(vip.getCountyName()).append("',")
                    .append("'").append(vip.getBelongsTownship()).append("',")
                    .append("'").append(vip.getBelongsVillage()).append("',")
                    .append("'").append(vip.getAreaCode()).append("',")
                    .append("'").append(vip.getLon()).append("',")
                    .append("'").append(vip.getLat()).append("',")
                    .append("'").append(vip.getHeight()).append("',")
                    .append("'").append(vip.getBusinessScope()).append("',")
                    .append("'").append(vip.getAreaScale()).append("',")
                    .append("'").append(vip.getAnnualProductionValue()).append("',")
                    .append("'").append(vip.getProdOperaCount()).append("',")
                    .append("'").append(vip.getTechCount()).append("',")
                    .append("'").append(vip.getTechAverageAge()).append("',")
                    .append("'").append(vip.getNetComputerAccess()).append("',")
                    .append("'").append(vip.getUseSmartPhones()).append("',")
                    .append("'").append(vip.getSmartPhoneBrand()).append("',")
                    .append("'").append(vip.getLeaderName()).append("',")
                    .append("'").append(vip.getLeaderPhone()).append("',")
                    .append("'").append(vip.getTechLeaderName()).append("',")
                    .append("'").append(vip.getTechLeaderPhone()).append("',")
                    .append("'").append(vip.getIntro()).append("',")
                    .append("'").append(vip.getEpath()).append("',")
                    .append("'").append(vip.getLongitude()).append("',")
                    .append("'").append(vip.getLatitude()).append("',")
                    .append("'").append(vip.getAddr()).append("',")
                    .append("'").append(vip.getDel_flg()).append("',")
                    .append("'").append(vip.getCreatetime()).append("',")
                    .append("\t);");
            return sql.toString();
        }
    }

    /**
     * 内部类 编写动态sql
     *
     * @author lxs
     */
    public class UserSql {
        public String updateUserById_sql(Map<String, Object> map) {
            User user = (User) map.get("user");
            StringBuilder sql = new StringBuilder();
            sql.append("UPDATE t_user SET ");
            if (user.getUsername() != null) {
                sql.append(" userName = " + "'" + user.getUsername() + "',");
            }
            if (user.getShowName() != null) {
                sql.append(" showName = " + "'" + user.getShowName() + "',");
            }
            if (user.getLoginName() != null) {
                sql.append(" loginName = " + "'" + user.getLoginName() + "',");
            }
            if (user.getMobile() != null) {
                sql.append(" mobile = " + "'" + user.getMobile() + "',");
            }
            if (user.getAddress() != null) {
                sql.append(" address = " + "'" + user.getAddress() + "',");
            }
            if (user.getAreaCode() != null) {
                sql.append(" areaCode = " + "'" + user.getAreaCode() + "',");
            }
            if (user.getIntroduction() != null) {
                sql.append(" Introduction = " + "'" + user.getIntroduction() + "',");
            }
            if (user.getEmail() != null) {
                sql.append(" Email = " + "'" + user.getEmail() + "',");
            }
            sql.append(" HeadImage = " + "'" + user.getHeadImage() + "'");
//            sql.append("latitude = " + "'"+user.getLatitude()+"'");
//            sql.append("longitude = " + "'"+user.getLongitude()+"'");
            sql.append(" WHERE id = " + user.getId());
            return sql.toString();
        }

        //按条件分页查询用户列表
        public String getUserListSql(Map<String, Object> map) {
            //获取分页数据
            Table table = (Table) map.get("table");
            //获取用户查询的条件
            User user = (User) map.get("user");
            //创建stringbuilder对象
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT\n" +
                    "\tt1.id id,\n" +
                    "\tt1.username username,\n" +
                    "\tt1.loginName loginName,\n" +
                    "\tt1.showName showName,\n" +
                    "\tt1.mobile mobile,\n" +
                    "\tt1.address address,\n" +
                    "\tt1.Email Email,\n" +
                    "\t(CASE t1.state\n" +
                    "\t\twhen -1 THEN\n" +
                    "'待审核'\n" +
                    "\t\tWHEN 0 THEN\n" +
                    "\t\t\t'通过'\n" +
                    "\t\tWHEN 1 THEN\n" +
                    "\t\t\t'未通过'\n" +
                    "\t\tELSE\n" +
                    "\t\t\t'黑名单'\n" +
                    "END) state,\n" +
                    "\tt1.areaCode areacode,\n" +
                    "\tt2.code rolecode,\n" +
                    "\tt2. NAME AS rolename,\n" +
                    "\tt3.areaname\n" +
                    "FROM\n" +
                    "\tt_user t1\n" +
                    "LEFT JOIN t_area t3 ON t1.areaCode = t3.areacode\n" +
                    "LEFT JOIN t_user_role t4 ON t4.userid = t1.id\n" +
                    "LEFT JOIN t_role t2 ON t2.id = t4.roleid\n");
            sql.append("where true\n");
            sql.append(" and t1.del_flg=0\n");
            if (user.getUsername() != null&&!"".equals(user.getUsername())) {
                sql.append("and t1.username like '%" + user.getUsername() + "%'\n");
            }
            if (user.getMobile() != null&&!"".equals(user.getMobile())) {
                sql.append("and t1.mobile like '%" + user.getMobile() + "%'\n");
            }
            if (user.getAreaCode() != null&&!"".equals(user.getAreaCode())) {
                sql.append("and t1.areacode like '" + user.getAreaCode() + "%'\n");
            }
            if (user.getLoginName() != null&&!"".equals(user.getLoginName())) {
                sql.append("and t1.loginName like '%" + user.getLoginName() + "%'\n");
            }
            if (user.getAddress() != null&&!"".equals(user.getAddress())) {
                sql.append("and t1.address like '%" + user.getAddress() + "%'\n");
            }
            if (!"".equals(user.getRolecode()) && user.getRolecode() != null) {
                sql.append("and t2.id='" + user.getRolecode() + "'\n");
            }
            if (user.getState() != null && !"".equals(user.getState())) {
                sql.append("and t1.state='" + user.getState() + "'\n");
            }
            if (user.getProvince() != null&&!"".equals(user.getProvince())) {
                sql.append("and t1.areacode like '" + user.getProvince() + "%'\n");
            }
            if (user.getCity() != null&&!"".equals(user.getCity())) {
                sql.append("and t1.areacode like '" + user.getCity() + "%'\n");
            }
            if (user.getArea() != null&&!"".equals(user.getArea())) {
                sql.append("and t1.areacode like '" + user.getArea() + "%'\n");
            }
            sql.append("group by id\n");
            sql.append("order by id asc limit\n" + table.getStartPoint() + "," + table.getLimit());
            sql.append(";");
            return sql.toString();
        }


        //App按条件分页查询用户列表
        public String getAppUserListSql(Map<String, Object> map) {
            //获取分页数据
            Table table = (Table) map.get("table");
            //获取用户查询的条件
            User user = (User) map.get("user");
            //创建stringbuilder对象
            StringBuilder sql = new StringBuilder();
            sql.append("select t1.*,t3.name rolename,t4.areaname areaname from t_user t1 join t_user_role t2 on t1.id = t2.userid \n" +
                    "LEFT JOIN t_role t3 on t3.id = t2.roleid\n" +
                    "LEFT JOIN t_area t4 on t1.areacode = t4.areacode\n");
            sql.append("where true\n");
            sql.append(" and t1.del_flg=0\n");
            if (user.getUsername() != null) {
                sql.append("and t1.username like '%" + user.getUsername() + "%'\n");
            }
            if (user.getMobile() != null) {
                sql.append("and t1.mobile like '%" + user.getMobile() + "%'\n");
            }
            if (user.getAreaCode() != null) {
                sql.append("and t1.areacode like '%" + user.getAreaCode() + "%'\n");
            }
//            if (user.getRolecode() != null) {
//                sql.append("and t1.rolecode like '%" + user.getRolecode() + "%'\n");
//            }

            sql.append("group by id\n");
            sql.append("order by id asc limit\n" + table.getStartPoint() + "," + table.getLimit());
            sql.append(";");
            return sql.toString();
        }

        //按条件查询用户列表总数
        public String getUserListCountSql(Map<String, Object> map) {
            //获取分页数据
            Table table = (Table) map.get("table");
            //获取用户查询的条件
            User user = (User) map.get("user");
            //创建stringbuilder对象
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT count(*) as count\n" +
                    "                    FROM (select a.roleid roleid,a.userid userid,b.areacode areaCode,b.username username,\n" +
                    "b.del_flg del_flg,b.mobile mobile,b.loginName loginName,b.address address,b.id id ,b.state from t_user_role a JOIN t_user b on a.userid = b.id) t1\n" +
                    "                    LEFT JOIN t_role t2 ON t1.roleid = t2. id\n" +
                    "                    left join t_area t3 on t1.areaCode = t3.areacode\n");
            sql.append("where true\n");
            sql.append(" and t1.del_flg=0\n");
            if (user.getUsername() != null&&!"".equals(user.getUsername())) {
                sql.append("and t1.username like '%" + user.getUsername() + "%'\n");
            }
            if (user.getMobile() != null&&!"".equals(user.getMobile())) {
                sql.append("and t1.mobile like '%" + user.getMobile() + "%'\n");
            }
            if (user.getAreaCode() != null&&!"".equals(user.getAreaCode())) {
                sql.append("and t1.areacode like '" + user.getAreaCode() + "%'\n");
            }
            if (user.getLoginName() != null&&!"".equals(user.getLoginName())) {
                sql.append("and t1.loginName like '%" + user.getLoginName() + "%'\n");
            }
            if (user.getAddress() != null&&!"".equals(user.getAddress())) {
                sql.append("and t1.address like '%" + user.getAddress() + "%'\n");
            }
            if (!"".equals(user.getRolecode()) && user.getRolecode() != null) {
                sql.append("and t2.id='" + user.getRolecode() + "'\n");
            }
            if (user.getState() != null && !"".equals(user.getState())) {
                sql.append("and t1.state='" + user.getState() + "'\n");
            }
            if (user.getProvince() != null&&!"".equals(user.getProvince())) {
                sql.append("and t1.areacode like '" + user.getProvince() + "%'\n");
            }
            if (user.getCity() != null&&!"".equals(user.getCity())) {
                sql.append("and t1.areacode like '" + user.getCity() + "%'\n");
            }
            if (user.getArea() != null&&!"".equals(user.getArea())) {
                sql.append("and t1.areacode like '" + user.getArea() + "%'\n");
            }
            sql.append(";");
            return sql.toString();
        }

        public String updateUser(Map<String, Object> map) {
            User user = (User) map.get("user");
            StringBuilder sql = new StringBuilder();
            sql.append("UPDATE t_user SET ");
            sql.append(" showName = " + "'" + user.getShowName() + "',");
            sql.append(" username = " + "'" + user.getUsername() + "',");
            sql.append(" loginName = " + "'" + user.getLoginName() + "',");
            sql.append(" mobile = " + "'" + user.getMobile() + "',");
            sql.append(" address = " + "'" + user.getAddress() + "',");
            sql.append(" areaCode = " + "'" + user.getAreaCode() + "',");
            sql.append(" Introduction = " + "'" + user.getIntroduction() + "',");
            sql.append(" HeadImage = " + "'" + user.getHeadImage() + "',");
            sql.append(" Email = " + "'" + user.getEmail() + "',");
            sql.append(" latitude = " + "'" + user.getLatitude() + "',");
            sql.append(" longitude = " + "'" + user.getLongitude() + "'");
//            sql.append(" rolecode = " + "'" + user.getRolecode() + "'");
            sql.append(" WHERE id = " + user.getId());
            return sql.toString();
        }

        public String register(Map<String, Object> map) {
            User user = (User) map.get("user");
            StringBuilder sql = new StringBuilder();
            sql.append("  insert ignore into  t_user(username,password,loginName,mobile,areaCode,latitude,showName,longitude");
            if (user.getAddress() != null) {
                sql.append(",address");
            }
            sql.append(") values(");
            sql.append("'").append(user.getUsername()).append("'").append(",");
            sql.append("'").append(user.getPassword()).append("'").append(",");
            sql.append("'").append(user.getLoginName()).append("'").append(",");
            sql.append("'").append(user.getMobile()).append("'").append(",");
            sql.append("'").append(user.getAreaCode()).append("'").append(",");
//            sql.append("'").append(user.getRolecode()).append("'").append(",");
            sql.append("'").append(user.getLatitude()).append("'").append(",");
            sql.append("'").append(user.getShowName()).append("'").append(",");
            sql.append("'").append(user.getLongitude()).append("'");
            if (user.getAddress() == null) {
                sql.append(");");
            } else {
                sql.append(",").append("'").append(user.getAddress()).append("'").append(");");
            }
            return sql.toString();
        }

        public String getChinaArea(Map<String, Object> map) {
            String areaCode = String.valueOf(map.get("areaCode"));
            String type = String.valueOf(map.get("type")) ;
            StringBuilder sql = new StringBuilder();
            sql.append("select * from t_area where 1=1 ");
            if (areaCode != null && !"".equals(areaCode)) {
                sql.append(" and areacode like '" + areaCode + "%'");
            }
            if (type.equals("city")) {
                sql.append(" and LENGTH(areacode)=4");
            }
            if(type.equals("county")){
                sql.append(" and LENGTH(areacode)=6");
            }
            return sql.toString();
        }

    }


}
